--
-- PostgreSQL database dump
--

-- Dumped from database version 17.5 (Ubuntu 17.5-1.pgdg22.04+1)
-- Dumped by pg_dump version 17.5 (Homebrew)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: get_policies_by_tags(text[]); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_policies_by_tags(tag_names text[]) RETURNS TABLE(policy_id text, policy_title text, matched_tags text[], match_count integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    WITH matched_policies AS (
        -- 匹配GB标签的政策
        SELECT 
            p.policy_id,
            p.policy_title,
            gb.tagname AS matched_tag
        FROM policy_content_table p
        JOIN policy_gb_label_table gbl ON p.policy_id = gbl.policy_id
        JOIN gb_industry_2_labels_table gb ON gbl.gb_industry_2 = gb.gb_policy_2
        WHERE gb.tagname = ANY(tag_names)
        
        UNION ALL
        
        -- 匹配ZX标签的政策
        SELECT 
            p.policy_id,
            p.policy_title,
            zx.tagname AS matched_tag
        FROM policy_content_table p
        JOIN policy_zx_label_table zxl ON p.policy_id = zxl.policy_id
        JOIN zx_industry_2_labels_table zx ON zxl.zx_industry_2 = zx.zx_policy_2
        WHERE zx.tagname = ANY(tag_names)
    )
    
    SELECT 
        p.policy_id,
        p.policy_title,
        ARRAY_AGG(DISTINCT mp.matched_tag) AS matched_tags,
        COUNT(DISTINCT mp.matched_tag) AS match_count
    FROM 
        policy_content_table p
    JOIN 
        matched_policies mp ON p.policy_id = mp.policy_id
    GROUP BY 
        p.policy_id, p.policy_title
    ORDER BY 
        match_count DESC, policy_title;
END;
$$;


ALTER FUNCTION public.get_policies_by_tags(tag_names text[]) OWNER TO postgres;

--
-- Name: get_policies_by_tags(text[], integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_policies_by_tags(tag_names text[], limit_count integer) RETURNS TABLE(policy_id text, policy_title text, matched_tags text[], match_count integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    WITH matched_policies AS (
        -- 匹配GB标签的政策
        SELECT 
            p.policy_id,
            p.title AS policy_title,
            gb.tag_name AS matched_tag
        FROM policy_content_table p
        JOIN policy_gb_label_table gbl ON p.policy_id = gbl.policy_id
        JOIN gb_industry_2_labels_table gb ON gbl.gb_industry_2 = gb.gb_industry_2
        WHERE gb.tag_name = ANY(tag_names)
        
        UNION ALL
        
        -- 匹配ZX标签的政策
        SELECT 
            p.policy_id,
            p.title AS policy_title,
            zx.tag_name AS matched_tag
        FROM policy_content_table p
        JOIN policy_zx_label_table zxl ON p.policy_id = zxl.policy_id
        JOIN zx_industry_2_labels_table zx ON zxl.zx_industry_2 = zx.zx_policy_2
        WHERE zx.tag_name = ANY(tag_names)
    )
    
    SELECT 
        p.policy_id,
        p.policy_title,
        ARRAY_AGG(DISTINCT mp.matched_tag) AS matched_tags,
        COUNT(DISTINCT mp.matched_tag) AS match_count
    FROM 
        policy_content_table p
    JOIN 
        matched_policies mp ON p.policy_id = mp.policy_id
    GROUP BY 
        p.policy_id, p.policy_title
    ORDER BY 
        match_count DESC, policy_title
    LIMIT limit_count;
END;
$$;


ALTER FUNCTION public.get_policies_by_tags(tag_names text[], limit_count integer) OWNER TO postgres;

--
-- Name: get_policy_covered_location_count(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_policy_covered_location_count() RETURNS TABLE(address_level text, covered_count integer)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT 'address_1'::text, COUNT(DISTINCT address_1)::integer
    FROM policy_content_table WHERE address_1 IS NOT NULL AND TRIM(address_1) <> ''
    UNION ALL
    SELECT 'address_2'::text, COUNT(DISTINCT address_2)::integer
    FROM policy_content_table WHERE address_2 IS NOT NULL AND TRIM(address_2) <> ''
    UNION ALL
    SELECT 'address_3'::text, COUNT(DISTINCT address_3)::integer
    FROM policy_content_table WHERE address_3 IS NOT NULL AND TRIM(address_3) <> ''
    UNION ALL
    SELECT 'address_4'::text, COUNT(DISTINCT address_4)::integer
    FROM policy_content_table WHERE address_4 IS NOT NULL AND TRIM(address_4) <> ''
    UNION ALL
    SELECT 'address_5'::text, COUNT(DISTINCT address_5)::integer
    FROM policy_content_table WHERE address_5 IS NOT NULL AND TRIM(address_5) <> ''
    UNION ALL
    SELECT 'address_6'::text, COUNT(DISTINCT address_6)::integer
    FROM policy_content_table WHERE address_6 IS NOT NULL AND TRIM(address_6) <> '';
END;
$$;


ALTER FUNCTION public.get_policy_covered_location_count() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: gb_industry_1_labels_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.gb_industry_1_labels_table (
    tag_number integer NOT NULL,
    tag_name character varying(255)
);


ALTER TABLE public.gb_industry_1_labels_table OWNER TO postgres;

--
-- Name: gb_industry_2_labels_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.gb_industry_2_labels_table (
    tag_name character varying(255) NOT NULL,
    gb_industry_1 integer NOT NULL,
    gb_industry_2 character varying(50) NOT NULL
);


ALTER TABLE public.gb_industry_2_labels_table OWNER TO postgres;

--
-- Name: locations_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.locations_table (
    location_id integer NOT NULL,
    location_name character varying(255),
    parent_location_id integer,
    location_id_path character varying(255),
    location_name_path character varying(255)
);


ALTER TABLE public.locations_table OWNER TO postgres;

--
-- Name: policy_ai_content_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.policy_ai_content_table (
    policy_id character varying(255) NOT NULL,
    ai_summary text,
    flowchart_json text,
    mindmap_time_json text,
    mindmap_space_json text,
    mindmap_theme_json text
);


ALTER TABLE public.policy_ai_content_table OWNER TO postgres;

--
-- Name: policy_content_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.policy_content_table (
    policy_id character varying(255) NOT NULL,
    title character varying(255) NOT NULL,
    write_time date,
    publish_time date,
    start_time date,
    end_time date,
    validity boolean,
    file_type character varying(255),
    department character varying(500),
    document_number character varying(255),
    main_text text NOT NULL,
    is_company_policy boolean,
    is_crossborder_policy boolean,
    level integer,
    type character varying(255),
    language character varying(255),
    address_1 character varying(255),
    address_2 character varying(255),
    address_3 character varying(255),
    address_4 character varying(255),
    address_5 character varying(255),
    address_6 character varying(255)
);


ALTER TABLE public.policy_content_table OWNER TO postgres;

--
-- Name: TABLE policy_content_table; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.policy_content_table IS 'The main policy table. Contains all the policies in db.';


--
-- Name: policy_gb_label_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.policy_gb_label_table (
    policy_id character varying(255) NOT NULL,
    gb_industry_1 integer NOT NULL,
    gb_industry_2 character varying(5) NOT NULL
);


ALTER TABLE public.policy_gb_label_table OWNER TO postgres;

--
-- Name: policy_reference_relations_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.policy_reference_relations_table (
    policy_id character varying(255) NOT NULL,
    cited_policy_id character varying(255)
);


ALTER TABLE public.policy_reference_relations_table OWNER TO postgres;

--
-- Name: policy_version_relations_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.policy_version_relations_table (
    policy_id character varying(255) NOT NULL,
    preceding_policy_id character varying(255)
);


ALTER TABLE public.policy_version_relations_table OWNER TO postgres;

--
-- Name: policy_zx_label_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.policy_zx_label_table (
    policy_id character varying(255) NOT NULL,
    zx_industry_1 integer NOT NULL,
    zx_industry_2 character varying(5) NOT NULL
);


ALTER TABLE public.policy_zx_label_table OWNER TO postgres;

--
-- Name: zx_industry_1_labels_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.zx_industry_1_labels_table (
    tag_number integer NOT NULL,
    tag_name character varying(255)
);


ALTER TABLE public.zx_industry_1_labels_table OWNER TO postgres;

--
-- Name: zx_industry_2_labels_table; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.zx_industry_2_labels_table (
    tag_name character varying(255),
    zx_policy_1 integer NOT NULL,
    zx_policy_2 character varying(50) NOT NULL
);


ALTER TABLE public.zx_industry_2_labels_table OWNER TO postgres;

--
-- Name: gb_industry_1_labels_table gb_industry_1_labels_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.gb_industry_1_labels_table
    ADD CONSTRAINT gb_industry_1_labels_table_pkey PRIMARY KEY (tag_number);


--
-- Name: gb_industry_2_labels_table gb_industry_2_labels_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.gb_industry_2_labels_table
    ADD CONSTRAINT gb_industry_2_labels_table_pkey PRIMARY KEY (gb_industry_2);


--
-- Name: locations_table locations_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.locations_table
    ADD CONSTRAINT locations_table_pkey PRIMARY KEY (location_id);


--
-- Name: policy_ai_content_table policy_ai_content_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_ai_content_table
    ADD CONSTRAINT policy_ai_content_table_pkey PRIMARY KEY (policy_id);


--
-- Name: policy_content_table policy_content_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_content_table
    ADD CONSTRAINT policy_content_table_pkey PRIMARY KEY (policy_id);


--
-- Name: policy_gb_label_table policy_gb_label_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_gb_label_table
    ADD CONSTRAINT policy_gb_label_table_pkey PRIMARY KEY (policy_id, gb_industry_1, gb_industry_2);


--
-- Name: policy_reference_relations_table policy_reference_relations_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_reference_relations_table
    ADD CONSTRAINT policy_reference_relations_table_pkey PRIMARY KEY (policy_id);


--
-- Name: policy_version_relations_table policy_version_relations_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_version_relations_table
    ADD CONSTRAINT policy_version_relations_table_pkey PRIMARY KEY (policy_id);


--
-- Name: policy_zx_label_table policy_zx_label_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_zx_label_table
    ADD CONSTRAINT policy_zx_label_table_pkey PRIMARY KEY (policy_id, zx_industry_1, zx_industry_2);


--
-- Name: zx_industry_1_labels_table zx_industry_1_labels_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.zx_industry_1_labels_table
    ADD CONSTRAINT zx_industry_1_labels_table_pkey PRIMARY KEY (tag_number);


--
-- Name: zx_industry_2_labels_table zx_industry_2_labels_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.zx_industry_2_labels_table
    ADD CONSTRAINT zx_industry_2_labels_table_pkey PRIMARY KEY (zx_policy_2);


--
-- Name: idx_gb_industry_2_labels_tag_name; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_gb_industry_2_labels_tag_name ON public.gb_industry_2_labels_table USING btree (tag_name);


--
-- Name: idx_policy_content_publish_time; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_policy_content_publish_time ON public.policy_content_table USING btree (publish_time);


--
-- Name: idx_policy_gb_label_policy_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_policy_gb_label_policy_id ON public.policy_gb_label_table USING btree (policy_id);


--
-- Name: idx_policy_zx_label_policy_id; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_policy_zx_label_policy_id ON public.policy_zx_label_table USING btree (policy_id);


--
-- Name: idx_zx_industry_2_labels_tag_name; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_zx_industry_2_labels_tag_name ON public.zx_industry_2_labels_table USING btree (tag_name);


--
-- Name: policy_ai_content_table fk_policy_id; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.policy_ai_content_table
    ADD CONSTRAINT fk_policy_id FOREIGN KEY (policy_id) REFERENCES public.policy_content_table(policy_id) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--

